package com.example.mapp.buildSql;

import com.example.entity.Elevator;
import com.example.entity.ReceiveHesuElevator;
import com.example.ext.entity.ExtElevator;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by simon on 2017/10/12.
 */
@SuppressWarnings("all")
public class ElevatorMappSql {

    public String queryList(ExtElevator extElevator){

        String select = "select a.*,b.party_name as maintenancePartyName from elevator a " +
                " left join party b on a.maintenance_party_id=b.id " +
                " left join sys_user e on a.safe_user_id = e.id "+
                "where a.used_party_id=#{usedPartyId}";
        if(extElevator.getElevatorNum()!=null && !extElevator.getElevatorNum().equals("")){
            select+=" and (a.elevator_num like '%"+extElevator.getElevatorNum()+"%' or a.outside_num like '%"+extElevator.getElevatorNum()+"%')";
        }

        if(extElevator.getUsedPartyId() != null){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.getMaintenancePartyId()!=null){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.getElevatorTypeId()!=null){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }
        if(extElevator.getProgramName()!=null){
            select+=" and program_name='"+extElevator.getProgramName()+"'";
        }
        if(StringUtils.isNotEmpty(extElevator.getIsPaperlessMaintenance())){
            if("0".equals(extElevator.getIsPaperlessMaintenance())){
                select+= " and (a.is_paperless_maintenance = '0' or a.is_paperless_maintenance is null)";
            }else{
                select+= " and (a.is_paperless_maintenance = '1')";
            }
        }
        if(StringUtils.isNotEmpty(extElevator.getIsInsure())){
            if("0".equals(extElevator.getIsInsure())){
                select+= " and (a.is_participate_in = '0' or a.is_participate_in is null)";
            }else {
                select+= " and (a.is_participate_in = '1')";
            }
        }
        if(extElevator.getSafeUserId() !=null){
            select+= " and a.safe_user_id = #{safeUserId}";
        }
        select+= " order  by  a.elevator_num asc ";
        return select;
    }

    /**
     * 电梯台账的初始化方法
     * @param extelevator
     * @return
     */
    public String queryElevatorListByExtElevator(ExtElevator extelevator){
        String partyType = extelevator.getPartyType();
        String partyLevel = extelevator.getPartyLevel();
        Long partyId = extelevator.getPartyId();
        StringBuilder builder = new StringBuilder("select e.*,p.party_name as maintenancePartyName, ");
        builder.append(" ifNull(is_paperless_maintenance,0),");
        builder.append(" (case when e.maintenance_user_first is null OR e.maintenance_user_second is null then 1 else 0 end) userIsNull,");
        builder.append(" (case when e.elevator_type_id is null  then 1 else 0 end) elevatorTypeIsNull,");
        builder.append(" up.party_name as usedPartyName ");
        builder.append(" from elevator e left join party p on e.maintenance_party_id=p.id");
        builder.append(" left join party up on e.used_party_id = up.id");
        builder.append(" where 1=1 ");
        // 设备编号查询
        if(extelevator.getElevatorNum() !=null){
            builder.append(" and (e.elevator_num like '%"+extelevator.getElevatorNum()+"%' or e.outside_num like '%"+extelevator.getElevatorNum()+"%')");
        }
        // 电梯类型
        if(extelevator.getElevatorTypeId()!=null){
            builder.append(" and e.elevator_type_id="+extelevator.getElevatorTypeId());
        }
        // 项目名称
        if(extelevator.getProgramName()!=null){
            builder.append(" and e.program_name='"+extelevator.getProgramName()+"'");
        }
        //使用单位查询条件
        if(extelevator.getMaintenancePartyId()!=null){
            builder.append(" and e.maintenance_party_id = "+extelevator.getMaintenancePartyId());
        }
        //维保单位查询条件
        if(extelevator.getUsedPartyId()!=null){
            builder.append(" and e.used_party_id = "+extelevator.getUsedPartyId());
        }
        //是否参与无纸化
        if(extelevator.getIsPaperlessMaintenance() != null){
            builder.append(" and e.is_paperless_maintenance = "+extelevator.getIsPaperlessMaintenance());
        }
        // 使用单位全部电梯
        if("2".equals(partyType) && partyId !=null ){
            builder.append(" and e.used_party_id = "+partyId);
        }
        // 维保单位全部电梯
        if("1".equals(partyType) && partyId !=null ){
            builder.append(" and e.maintenance_party_id = "+partyId);
        }
        //维保人员
        if(extelevator.getMaintenanceUserFirst()!=null && !extelevator.getMaintenanceUserFirst().equals("") ){
            builder.append(" and (e.maintenance_user_first="+extelevator.getMaintenanceUserFirst()+" or e.maintenance_user_second="+extelevator.getMaintenanceUserFirst()+")");
        }
        //安全管理员
        if(extelevator.getSafeUserId() !=null){
            builder.append(" and e.safe_user_id = "+extelevator.getSafeUserId());
        }
        // 局
        if("4".equals(partyType) &&  "2".equals(partyLevel)){
            builder.append(" and e.branch_office in  (select id from party where parent_id="+partyId+" and party_type='4')");
        }
        // 分局
        if("4".equals(partyType) &&  "3".equals(partyLevel)){
            builder.append(" and e.branch_office = "+partyId+" ");
        }
        builder.append(" order  by elevatorTypeIsNull desc,userIsNull desc , e.elevator_num asc");
        return builder.toString();
    }

    /**
     * 查询使用单位
     * @param extElevator
     * @return
     */
    public String queryUsedParty(ExtElevator extElevator){
        Long partyId = extElevator.getPartyId();
        String partyType = extElevator.getPartyType();
        String partyLevel = extElevator.getPartyLevel();
        StringBuilder builder = new StringBuilder();
        // 超管+市局
        builder.append(" select DISTINCT er.used_party_id as usedPartyId ,py.party_name as usedPartyName ");
        builder.append(" from elevator er");
        builder.append(" left join party py ON er.used_party_id = py.id");
        builder.append(" where  py.party_name is not null and py.party_type='2' ");

        // 维保所有项目名称
        if("1".equals(partyType) && partyId !=null ){
            builder.append(" and er.maintenance_party_id = "+partyId);
        }
        if("2".equals(partyType) && partyId !=null ){
            builder.append(" and er.used_party_id = "+partyId);
        }
        // 局
        if("4".equals(partyType) &&  "2".equals(partyLevel)){
            builder.append(" and er.branch_office in  (select id from party where parent_id="+partyId+" and party_type='4')");
        }
        // 分局
        if("4".equals(partyType) &&  "3".equals(partyLevel)){
            builder.append(" and er.branch_office = "+partyId+" ");
        }
        return builder.toString();
    }

    /**
     * 查询维保单位
     * @param extElevator
     * @return
     */
    public String queryMainParty(ExtElevator extElevator){
        Long partyId = extElevator.getPartyId();
        String partyType = extElevator.getPartyType();
        String partyLevel = extElevator.getPartyLevel();
        StringBuilder builder = new StringBuilder();
        // 超管+市局
        builder.append(" select DISTINCT er.maintenance_party_id as maintenancePartyId ,py.party_name as maintenancePartyName ");
        builder.append(" from elevator er");
        builder.append(" left join party py ON er.maintenance_party_id = py.id");
        builder.append(" where  py.party_name is not null and py.party_type='1' ");
        // 维保所有项目名称
        if("1".equals(partyType) && partyId !=null ){
            builder.append(" and er.maintenance_party_id = "+partyId);
        }
        if("2".equals(partyType) && partyId !=null ){
            builder.append(" and er.used_party_id = "+partyId);
        }
        // 局
        if("4".equals(partyType) &&  "2".equals(partyLevel)){
            builder.append(" and er.branch_office in  (select id from party where parent_id="+partyId+" and party_type='4')");
        }
        // 分局
        if("4".equals(partyType) &&  "3".equals(partyLevel)){
            builder.append(" and er.branch_office = "+partyId+" ");
        }
        return builder.toString();
    };

    /**
     * 查询项目名称
     * @param extElevator
     * @return
     */
    public  String queryProgramName(ExtElevator extElevator){
        Long partyId = extElevator.getPartyId();
        String partyType = extElevator.getPartyType();
        String partyLevel = extElevator.getPartyLevel();
        StringBuilder builder = new StringBuilder();
        // 超管+市局
        builder.append("SELECT DISTINCT e.program_name programName FROM elevator e ");
        builder.append(" where e.program_name is not null ");
        // 维保所有项目名称
        if("1".equals(partyType) && partyId !=null ){
            builder.append(" and e.maintenance_party_id = "+partyId);
        }
        if("2".equals(partyType) && partyId !=null ){
            builder.append(" and e.used_party_id = "+partyId);
        }
        // 局
        if("4".equals(partyType) &&  "2".equals(partyLevel)){
            builder.append(" and e.branch_office in  (select id from party where parent_id="+partyId+" and party_type='4')");
        }
        // 分局
        if("4".equals(partyType) &&  "3".equals(partyLevel)){
            builder.append(" and e.branch_office = "+partyId+" ");
        }
        return builder.toString();
    }

    public String queryMaintList(Map<String, Object> extElevator){

        String select = "select a.*,b.party_name as usedPartyName from elevator a  " +
                " left join party b on a.used_party_id=b.id  " +
                "where a.maintenance_party_id=#{partyId}";
        if(extElevator.get("elevatorNum")!=null && !extElevator.get("elevatorNum").equals("")){
            select+=" and (a.elevator_num like '%"+extElevator.get("elevatorNum")+"%' or a.outside_num like '%"+extElevator.get("elevatorNum")+"%')";
        }
        if(extElevator.get("usedPartyId")!=null && !extElevator.get("usedPartyId").equals("") ){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.get("maintenancePartyId")!=null && !extElevator.get("maintenancePartyId").equals("") ){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.get("elevatorTypeId")!=null && !extElevator.get("elevatorTypeId").equals("") ){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }
        if(extElevator.get("programName")!=null){
            select+=" and program_name='"+extElevator.get("programName")+"'";
        }
        if(extElevator.get("maintenanceUserFirst")!=null && !extElevator.get("maintenanceUserFirst").equals("") ){
            select+=" and (a.maintenance_user_first=#{maintenanceUserFirst} or a.maintenance_user_second=#{maintenanceUserFirst})";
        }
        if(extElevator.get("isPaperlessMaintenance") != null && !"".equals(extElevator.get("isPaperlessMaintenance"))){
            if("0".equals(extElevator.get("isPaperlessMaintenance"))){
                select+= " and (a.is_paperless_maintenance = '0' or a.is_paperless_maintenance is null)";
            }else{
                select+= " and (a.is_paperless_maintenance = '1')";
            }
        }
        select+= " order  by  a.elevator_num asc ";
        return select;
    }

    public String queryJgList(Map<String, Object> extElevator){
        String select = "select a.*,";
        String select1 = " c.party_name as maintenancePartyName,b.party_name as usedPartyName" ;
        String select2 = " b.party_name as maintenancePartyName,c.party_name as usedPartyName";
        String select3 = " from elevator a" +
                " left join party b on a.used_party_id=b.id  " +
                " left join party c on a.maintenance_party_id=c.id " +
                " where 1=1 ";
        if(extElevator.get("partyId")!=null && !extElevator.get("partyId").equals("") ){
            select += select1+" and a.used_party_id in (select id from party where parent_id=#{partyId})";
        }
        if(extElevator.get("elevatorNum")!=null && !extElevator.get("elevatorNum").equals("") ){
            select+=" and (a.elevator_num like '%"+extElevator.get("elevatorNum")+"%' or a.outside_num like '%"+extElevator.get("elevatorNum")+"%')";
        }
        if(extElevator.get("usedPartyId")!=null && !extElevator.get("usedPartyId").equals("") ){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.get("maintenancePartyId")!=null && !extElevator.get("maintenancePartyId").equals("") ){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.get("elevatorTypeId")!=null && !extElevator.get("elevatorTypeId").equals("") ){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }

        //添加监管部门查询电梯时，根据电梯所在分局筛选
        List<String> branchOffices = (List<String>) extElevator.get("branchOffices");
        if(branchOffices != null && branchOffices.size() > 0){
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < branchOffices.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{branchOffices["+i+"]}");
                } else {
                    str.append(", #{branchOffices["+i+"]}");
                }
            }
            str.append(")");
            select += " and a.branch_office in "+str.toString();
        }
        if(extElevator.get("isPaperlessMaintenance") != null && !"".equals(extElevator.get("isPaperlessMaintenance"))){
            if("0".equals(extElevator.get("isPaperlessMaintenance"))){
                select+= " and (a.is_paperless_maintenance = '0' or a.is_paperless_maintenance is null)";
            }else{
                select+= " and (a.is_paperless_maintenance = '1')";
            }
        }
        select+= " order  by  a.elevator_num asc ";
        return select;
    }

    public String getLatLng(Map<String, Object> extElevator,String mark) {

		String select = "SELECT A.ELEVATOR_NUM as elevatorNum,A.LNG as lng,A.LAT as lat,A.ELEVATOR_PLACE as elevatorPlace,A.PLACE_TYPE as placeType,"
				+ "B.TYPE_NAME as typeName FROM ELEVATOR A "
				+ "LEFT JOIN ELEVATOR_TYPE B ON A.ELEVATOR_TYPE_ID = B.ID";
//        if(extElevator.get("partyId")!=null && extElevator.get("partyId")!="" ){
//            select += " and a.used_party_id in (select id from party where parent_id=#{partyId})";
//        }
        String partyId = Integer.toString((Integer) extElevator.get("partyId"));
        //如果是市监管部门
        if ("0".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id IN (" +
                    "SELECT id FROM party WHERE parent_id = "
                    + partyId + " And party_type = '4') And party_type = '2')";
        }
        //如果是区监管部门
        if ("1".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id = " + partyId + " And party_type = '2')";
        }
        //如果是分局监管部门
        if ("2".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id = "+
                    " (SELECT parent_id FROM party where id = " + partyId +")"+
                    " And party_type = '2')";
        }
        return select;
    }


    public String getLatLng_bak(Map<String, Object> extElevator) {

		String select = "SELECT A.ELEVATOR_NUM as elevatorNum,A.LNG as lng,A.LAT as lat,CASE WHEN A.NEXT_CHECK_DATE < DATE_FORMAT(NOW(),'%Y%M%D') THEN '0' ELSE '1' END  checkStatus,"
				+ "CASE WHEN A.NEXT_HALFMON_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_QUARTERLY_MAINTE_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_HALFYEAR_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_YEAR_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') THEN '0' ELSE '1' END maintStatus FROM ELEVATOR A ";
//        if(extElevator.get("partyId")!=null && extElevator.get("partyId")!="" ){
//            select += " and a.used_party_id in (select id from party where parent_id=#{partyId})";
//        }
        return select;
    }

    public String updateSafeUser(Map<String,Object> inParam) {
        String update = "update elevator set safe_user_id = #{safeUserId} where 1=1";
        List<Integer> elevatorNums = (List<Integer>) inParam.get("elevatorNums");
        if (!"0".equals(inParam.get("partyType")) && elevatorNums != null && elevatorNums.size() > 0) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < elevatorNums.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{elevatorNums[" + i + "]}");
                } else {
                    str.append(", #{elevatorNums[" + i + "]}");
                }
            }
            str.append(")");
            update += " and elevator_num in " + str.toString();
        }
        return update;
    }
    public String updateChessboard(Map<String,Object> inParam) {
        Integer  branch_office_id = (Integer) inParam.get("branchOfficeId");

        String update = "update elevator set branch_office = "+branch_office_id+" where 1=1";
        List<Integer> elevatorNums = (List<Integer>) inParam.get("elevatorNums");
        if (!"0".equals(inParam.get("partyType")) && elevatorNums != null && elevatorNums.size() > 0) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < elevatorNums.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{elevatorNums[" + i + "]}");
                } else {
                    str.append(", #{elevatorNums[" + i + "]}");
                }
            }
            str.append(")");
            update += " and elevator_num in " + str.toString();
        }
        return update;
    }

    /**
     * 修改电梯类型
     * @param param
     * @return
     */
    public String updateElevatorType(Map<String,Object> param){
        String  elevator_type_id = (String) param.get("elevatorTypeId");

        String update = "update elevator set elevator_type_id = "+elevator_type_id+" where 1=1";
        List<Integer> elevatorNums = (List<Integer>) param.get("elevatorNums");
        if (!"0".equals(param.get("partyType")) && elevatorNums != null && elevatorNums.size() > 0) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < elevatorNums.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{elevatorNums[" + i + "]}");
                } else {
                    str.append(", #{elevatorNums[" + i + "]}");
                }
            }
            str.append(")");
            update += " and elevator_num in " + str.toString();
        }
        return update;
    }

    /**
     * 按需维保，获取可选电梯
     * @param extElevator
     * @return
     */
    public String queryElevatorList(ExtElevator extElevator){
        StringBuilder builder = new StringBuilder();
        builder.append("select * from(\n" +
                "select a.id, a.used_party_id,a.maintenance_party_id, a.outside_num as outsidenum, a.create_company_name," +
                "       a.build_addr as buildAddr,a.elevator_type_id as elevatorTypeId, " +
                "       a.elevator_num as elevatorNum, e.type_name as elevatorTypeName,\n" +
                "       b.party_name as maintenancePartyName,g.party_name as usedPartyName, c.user_name as safeUserName,\n" +
                "       d.user_name as maintenanceUserFirstName,\n" +
                "       d.firstgt,\n" +
                "       case when insurance_num is not null then '是'\n" +
                "       else '否' end as isInsure\n" +
                "from elevator a\n" +
                "left join elevator_type e on e.id = a.elevator_type_id\n" +
                "left join party b on a.maintenance_party_id=b.id\n" +
                "left join party g on a.used_party_id=g.id "+
                "left join sys_user c on a.safe_user_id = c.id\n" +
                "left join sys_user d on a.maintenance_user_first = d.id) S where 1=1 ");

        if(extElevator.getUsedPartyId() != null){
            builder.append(" and used_party_id = #{usedPartyId}");
        }

        if(StringUtils.isNotEmpty(extElevator.getIsInsure())){
            builder.append(" and isInsure = #{isInsure}");
        }
        if(extElevator.getMaintenancePartyId() != null){
            builder.append(" and maintenance_party_id = #{maintenancePartyId}");
        }

        //电梯id
        List<String> elevatorIds = extElevator.getElevatorIds();
        if(!CollectionUtils.isEmpty(elevatorIds)){
            StringBuilder sb1 = new StringBuilder(" and id in (");
            for(int i = 0,len = elevatorIds.size(); i < len; i++){
                if(i == len - 1){
                    sb1.append("'").append(elevatorIds.get(i)).append("')");
                }else{
                    sb1.append("'").append(elevatorIds.get(i)).append("',");
                }
            }
            builder.append(sb1);
        }

        //制造单位，可能多个值
        String createCompanyName = extElevator.getCreateCompanyName();
        if(StringUtils.isNotEmpty(createCompanyName)){
            String [] names = createCompanyName.split(",");
            StringBuilder sb = new StringBuilder(" and create_company_name in (");
            for(int i=0, len = names.length; i < len; i++){
                if(i == len - 1){
                    sb.append("'").append(names[i]).append("')");
                }else{
                    sb.append("'").append(names[i]).append("',");
                }
            }
            builder.append(sb);
        }
        if(extElevator.getElevatorTypeId() != null){
            builder.append(" and elevatorTypeId=#{elevatorTypeId}");
        }

        if(StringUtils.isNotEmpty(extElevator.getElevatorNum())){
            builder.append(" and elevatorNum like '%"+extElevator.getElevatorNum()+"%'");
        }

        //添加多种类型电梯的查询
        List<String> elevatorTypeIds = extElevator.getElevatorTypeIds();
        if(!CollectionUtils.isEmpty(elevatorTypeIds)){
            StringBuilder sb = new StringBuilder(" and elevatorTypeId in (");
            for(int i = 0,len = elevatorTypeIds.size(); i < len; i++){
                String elevatorTypeId = elevatorTypeIds.get(i);
                if(i == len - 1){
                    sb.append("'").append(elevatorTypeId).append("')");
                }else{
                    sb.append("'").append(elevatorTypeId).append("',");
                }
            }
            builder.append(sb);
        }
        builder.append(" order by used_party_id desc ");

        return builder.toString();
    }

    /**
     * 无纸化维保获取可选电梯
     * @param extElevator
     * @return
     */
    public String queryPaperlessElevatorList(ExtElevator extElevator){
        StringBuilder builder = new StringBuilder();
        builder.append("select * from(\n" +
                "                select a.id, a.used_party_id,a.maintenance_party_id, a.outside_num as outsidenum, a.create_company_name,\n" +
                "                       a.build_addr as buildAddr,a.elevator_type_id as elevatorTypeId,\n" +
                "                       a.elevator_num as elevatorNum, e.type_name as elevatorTypeName,\n" +
                "                       a.branch_office as branchOffice,\n" +
                "                       a.is_paperless_maintenance,\n"+
                "                       b.party_name as maintenancePartyName,c.user_name as safeUserName,\n" +
                "                       d.user_name as maintenanceUserFirstName,\n" +
                "                       d.firstgt,\n" +
                "                       f.parent_id as parentId,\n" +
                "                       case when insurance_num is not null then '是'else '否' end as isInsure,\n" +
                "                        e.maintCount,\n" +
                "                       case when maintCount > 2 then '是' else '否' end as isMaint\n" +
                "                from elevator a\n" +
                "                left join elevator_type e on e.id = a.elevator_type_id\n" +
                "                left join party b on a.maintenance_party_id=b.id\n" +
                "                left join sys_user c on a.safe_user_id = c.id\n" +
                "                left join sys_user d on a.maintenance_user_first = d.id\n" +
                "                left join (select COUNT(1) as maintCount,elevator_num from elevator.traction_half_mon  where elevator_num is not null group by elevator_num) e\n" +
                "                    on a.elevator_num = e.elevator_num\n" +
                "                left join  sub_party_info f on f.id =  a.branch_office\n" +
                "                ) S where 1=1 ");

        if(extElevator.getUsedPartyId() != null){
            builder.append(" and used_party_id = #{usedPartyId}");
        }
        if(extElevator.getMaintenancePartyId() != null){
            builder.append(" and maintenance_party_id = #{maintenancePartyId}");
        }

        //电梯id
        List<String> elevatorIds = extElevator.getElevatorIds();
        if(!CollectionUtils.isEmpty(elevatorIds)){
            StringBuilder sb1 = new StringBuilder(" and id in (");
            for(int i = 0,len = elevatorIds.size(); i < len; i++){
                if(i == len - 1){
                    sb1.append("'").append(elevatorIds.get(i)).append("')");
                }else{
                    sb1.append("'").append(elevatorIds.get(i)).append("',");
                }
            }
            builder.append(sb1);
        }

        //电梯台账参数不为空，是查询已添加申请配置的电梯。
        if(CollectionUtils.isEmpty(elevatorIds) && StringUtils.isEmpty(extElevator.getIsPaperlessMaintenance())){
            builder.append(" and (is_paperless_maintenance = '0' or is_paperless_maintenance is null)");
        }

        //制造单位，可能多个值
        String createCompanyName = extElevator.getCreateCompanyName();
        if(StringUtils.isNotEmpty(createCompanyName)){
            String [] names = createCompanyName.split(",");
            StringBuilder sb = new StringBuilder(" and create_company_name in (");
            for(int i=0, len = names.length; i < len; i++){
                if(i == len - 1){
                    sb.append("'").append(names[i]).append("')");
                }else{
                    sb.append("'").append(names[i]).append("',");
                }
            }
            builder.append(sb);
        }
        if(extElevator.getElevatorTypeId() != null){
            builder.append(" and elevatorTypeId=#{elevatorTypeId}");
        }

        if(StringUtils.isNotEmpty(extElevator.getElevatorNum())){
            builder.append(" and elevatorNum=#{elevatorNum}");
        }

        //添加多种类型电梯的查询
        List<String> elevatorTypeIds = extElevator.getElevatorTypeIds();
        if(!CollectionUtils.isEmpty(elevatorTypeIds)){
            StringBuilder sb = new StringBuilder(" and elevatorTypeId in (");
            for(int i = 0,len = elevatorTypeIds.size(); i < len; i++){
                String elevatorTypeId = elevatorTypeIds.get(i);
                if(i == len - 1){
                    sb.append("'").append(elevatorTypeId).append("')");
                }else{
                    sb.append("'").append(elevatorTypeId).append("',");
                }
            }
            builder.append(sb);
        }

        if(extElevator.getIsMaint() != null){
            builder.append(" and ismaint = #{isMaint}");
        }

        if(extElevator.getJgPartyId() != null){
            builder.append(" and parentId = #{jgPartyId}");
        }
        builder.append(" order by id desc ");

        return builder.toString();
    }


    /**
     * 按需维保更新电梯是否参与维保与维保变更天数
     */
    public String elevatorDemandMaintanceDay(Map inMap){
        StringBuilder builder = new StringBuilder("update elevator a set ");
        StringBuilder sb = new StringBuilder();
        if(inMap.get("isParticipateIn") != null && !"".equals(inMap.get("isParticipateIn"))){
            sb.append(" is_participate_in = #{isParticipateIn},");
        }
        if(inMap.get("maintenanceDay") != null && !"".equals(inMap.get("maintenanceDay"))){
            sb.append(" maintenance_day = #{maintenanceDay},");
            sb.append(" next_halfmon_maint_date = date_add(last_maintenance_time,INTERVAL ").append(inMap.get("maintenanceDay")).append(" day) + 0,");
        }
        builder.append(sb.substring(0,sb.lastIndexOf(","))).append(" where 1=1 ");
        if(inMap.get("elevatorIds") != null){
            builder.append(" and id in (");
            List<String> elevatorIds = (List<String>) inMap.get("elevatorIds");
            for(int i = 0,len = elevatorIds.size(); i < len; i++){
                if(i == len - 1){
                    builder.append("'").append(elevatorIds.get(i)).append("'");
                }else{
                    builder.append("'").append(elevatorIds.get(i)).append("',");
                }

            }
            builder.append(")");
        }
        return builder.toString();
    }

    /**
     *
     * 双随机一公开查询电梯信息
     * findList
     * @param
     * @return
     */
    public String getElevatorList(Map map){
        StringBuffer sb = new StringBuffer();

        sb.append(" select * from ( ");
        sb.append(" select ");
        sb.append("  r.elevator_num as 'elevatorNum', ");
        sb.append("  r.build_addr as 'buildAddr', ");
        sb.append("  r.elevator_type_id as 'elevatorTypeId', ");
        sb.append("  r.elevator_place as 'elevatorPlace' ,");
        sb.append("  r.used_party_id as 'usedPartyId',");
        sb.append("  r.maintenance_party_id as 'maintenancePartyId', ");
        sb.append("  r.branch_office, ");
        sb.append("  pe.type_name as 'typeName', ");
        sb.append("  ty.party_name as 'maintenancePartyName', ");
        sb.append("  pa.party_name as 'usedPartyName', ");
        sb.append("  fj.parent_id, ");
        sb.append("  (case when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=0 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <10 then 0 ");
        sb.append("  when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=10 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=15 then 1 ");
        sb.append("  when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=15 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=20 then 2 ");
        sb.append("  else 3 end) year");
        sb.append("  from elevator r ");
        sb.append(" left join elevator_type pe ON r.elevator_type_id = pe.id ");
        sb.append(" left join party pa ON r.used_party_id =pa.id ");
        sb.append(" left join party ty ON r.maintenance_party_id = ty.id ");
        sb.append(" left join party fj on fj.id = r.branch_office ");
        sb.append(" ) ta ");

        sb.append(" where 1=1 ");

        /**
         * 根据监管单位不同级别，过滤电梯，当为市局时不需要加此条件
         */
        if(map.get("partyLevel") != null && !"".equals(map.get("partyLevel"))
            && map.get("parentId") != null && !"".equals((map.get("parentId")))){
            //区级监管单位
            if("2".equals(map.get("partyLevel"))){
                sb.append(" and parent_id = #{parentId}");
            }
            //分局监管单位
            else if("3".equals(map.get("partyLevel"))){
                sb.append(" and branch_office = #{parentId}");
            }
        }

        if(map.get("elevatorTypeParam") !=null && !"".equals(map.get("elevatorTypeParam"))){
            sb.append(" and ta.elevatorTypeId in ("+map.get("elevatorTypeParam").toString()+") ");
        }

        if( map.get("elevatorPlaceParam") !=null){
            List<String> types = (List<String>) map.get("elevatorPlaceParam");
            StringBuilder builder = new StringBuilder(" and ta.elevatorPlace in ( ");
            for (int i = 0; i < types.size(); i++) {
                String name = types.get(i);
                if (i == types.size() - 1) {
                    builder.append("'").append(name).append("'");
                } else {
                    builder.append("'").append(name).append("'").append(",");
                }
            }
            builder.append(")");
            sb.append(builder);
        }


        if(map.get("elevatorAgeParam") !=null && !"".equals(map.get("elevatorAgeParam"))){
            sb.append(" and ta.year in ("+map.get("elevatorAgeParam").toString()+") ");
        }

        return sb.toString();
    }


    public String detectionGetElevator(Map map){

        StringBuilder sb = new StringBuilder(" select * from (\n" +
                "        select\n" +
                "         r.id as 'id',\n" +
                "         r.elevator_num as 'elevatorNum',\n" +
                "         r.build_addr as 'buildAddr',\n" +
                "         r.elevator_type_id as 'elevatorTypeId',\n" +
                "         r.elevator_place as 'elevatorPlace' ,\n" +
                "         r.used_party_id as 'usedPartyId',\n" +
                "         r.maintenance_party_id as 'maintenancePartyId',\n" +
                "         pe.type_name as 'typeName',\n" +
                "         ty.party_name as 'maintenancePartyName',\n" +
                "         pa.party_name as 'usedPartyName',\n" +
                "         (case when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=0 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <10 then 0\n" +
                "         when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=10 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=15 then 1\n" +
                "         when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=15 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=20 then 2\n" +
                "         else 3 end) year\n" +
                "         from elevator r\n" +
                "        left join elevator_type pe ON r.elevator_type_id = pe.id\n" +
                "        left join party pa ON r.used_party_id =pa.id\n" +
                "        left join party ty ON r.maintenance_party_id = ty.id\n" +
                "        ) ta");
        sb.append(" where 1=1 ");

        if(map.get("elevatorTypeParam") !=null && !"".equals(map.get("elevatorTypeParam"))){
            sb.append(" and ta.elevatorTypeId in ("+map.get("elevatorTypeParam").toString()+") ");
        }

        if( map.get("elevatorPlaceParam") !=null){
            List<String> types = (List<String>) map.get("elevatorPlaceParam");
            StringBuilder builder = new StringBuilder(" and ta.elevatorPlace in ( ");
            for (int i = 0; i < types.size(); i++) {
                String name = types.get(i);
                if (i == types.size() - 1) {
                    builder.append("'").append(name).append("'");
                } else {
                    builder.append("'").append(name).append("'").append(",");
                }
            }
            builder.append(")");
            sb.append(builder);
        }

        if(map.get("elevatorAgeParam") !=null && !"".equals(map.get("elevatorAgeParam"))){
            sb.append(" and ta.year in ("+map.get("elevatorAgeParam").toString()+") ");
        }

        if(map.get("parentId") != null && !"".equals((map.get("parentId")))){
            sb.append(" and parent_id = #{parentId}");
        }

        if(map.get("usedPartyId") != null && !"".equals(map.get("usedPartyId"))){
            sb.append(" and usedPartyId = #{usedPartyId}");
        }

        if(map.get("maintenancePartyId") != null && !"".equals(map.get("maintenancePartyId"))){
            sb.append(" and maintenancePartyId = #{maintenancePartyId}");
        }

        if(map.get("elevatorNum") != null && !"".equals(map.get("elevatorNum"))){
            sb.append(" and elevatorNum = #{elevatorNum}");
        }
        return sb.toString();
    }

    /**
     * 电梯台账更新保险信息
     * @param map
     * @return
     */
    public String updateInstanceDate(Map map){
        StringBuilder builder = new StringBuilder("update elevator set");
        StringBuilder setValue = new StringBuilder();
        if(map.get("insuranceNum") != null && !"".equals(map.get("insuranceNum"))){
            setValue.append("  insurance_num = #{insuranceNum},");
        }
        if(map.get("insuranceName") != null && !"".equals(map.get("insuranceName"))){
            setValue.append("  insurance_name = #{insuranceName},");
        }
        if(map.get("insuranceDate") != null && !"".equals(map.get("insuranceDate"))){
            setValue.append("  insurance_date = #{insuranceDate},");
        }
        builder.append(setValue.substring(0,setValue.lastIndexOf(","))).append(" where 1=1 ");

        if(map.get("ids") != null){
            List<String> ids = (List<String>)map.get("ids");
            StringBuilder where = new StringBuilder(" and id in ( ");
            for (int i = 0; i < ids.size(); i++) {
                String name = ids.get(i);
                if (i == ids.size() - 1) {
                    where.append("'").append(name).append("'");
                } else {
                    where.append("'").append(name).append("'").append(",");
                }
            }
            where.append(")");
            builder.append(where);
        }

        return builder.toString();
    }

    /**
     * 获取电梯制造电梯
     * @param elevator
     * @return
     */
    public String getElevatorCreateComanys(ExtElevator elevator){
        StringBuilder builder = new StringBuilder("select distinct create_company_name as createCompanyName from elevator where 1=1 ");
        if(elevator.getUsedPartyId() != null){
            builder.append(" and used_party_id=#{usedPartyId}");
        }
        if(elevator.getMaintenancePartyId() != null){
            builder.append(" and maintenance_party_id=#{maintenancePartyId}");
        }
        return builder.toString();
    }

    /**
     * 无纸化维保更新参与无纸化维保标识
     * @param map isPaperlessMaintenance
     * @return
     */
    public String updatePaperLessElevators(Map map){
        StringBuilder builder = new StringBuilder("update elevator ");
        String isPaperlessMaintenance = (String)map.get("isPaperlessMaintenance");
        String elevatorIds = (String)map.get("elevatorIds");
        if(isPaperlessMaintenance != null && !"".equals(isPaperlessMaintenance)){
            builder.append(" set is_paperless_maintenance = #{isPaperlessMaintenance}");
        }
        if(elevatorIds != null && !"".equals(elevatorIds)){
            builder.append(" where id in ").append(" (").append(elevatorIds).append(")");
        }
        return builder.toString();
    }

    public String updateProjectName(Map map){
        StringBuilder builder = new StringBuilder("update elevator ");
        String programName = (String)map.get("programName");
        String elevatorIds = (String)map.get("elevatorIds");
        if(programName != null && !"".equals(programName)){
            builder.append(" set program_name = #{programName}");
        }
        if(elevatorIds != null && !"".equals(elevatorIds)){
            builder.append(" where id in ").append(" (").append(elevatorIds).append(")");
        }
        return builder.toString();
    }

    /**
     * 查询
     * @param
     * @return
     */
    public String findNoFlag(Map map) {
        StringBuilder builder = new StringBuilder();
        builder.append(" select  id as id,angle as angle,be_used_date as beUserDate, ");
        builder.append("  brand_name as brandName,build_addr as buildAddr, build_party_name as buildPartyName,  ");
        builder.append(" building_info as buildingInfo,create_company_name as createCompanyName,drive_way as driveWay, ");
        builder.append(" elevator_addr as elevatorAddr,elevator_area as elevatorArea,elevator_building_num as elevatorBuildingNum, ");
        builder.append(" elevator_city as elevatorCity,elevator_model as elevatorModel,elevator_num as elevatorNum, ");
        builder.append(" elevator_photo_url as elevatorPhotoUrl, elevator_province as elevatorProvince,elevator_street as elevatorStreet, ");
        builder.append(" elevator_type_id as elevatorTypeId,examination_party_id as examinationPartyId,examination_party_name as examinationPartyName, ");
        builder.append(" floor_station as floorStation,governor_ck_date as governorCkDate,insurance_date as insuranceDate,insurance_name as insuranceName, ");
        builder.append(" insurance_num as insuranceNum,is_stop as isStop,last_maintenance_time as lastMaintenanceTime,lat as lat,lift_height as liftHeight, ");
        builder.append(" lng as lng,maint_tel as maintTel,maintenance_party_id as maintenancePartyId,maintenance_user_first as maintenanceUserFirst, ");
        builder.append(" maintenance_user_second as maintenanceUserSecond,next_check_date as nextCheckDate,next_halfmon_maint_date as nextHalfmonMaintDate, ");
        builder.append(" next_halfyear_maint_date as nextHalfyearMaintDate,next_quarterly_mainte_date as nextQuarterlyMainteDate,next_year_maint_date as nextYearMaintDate, ");
        builder.append(" outside_num as outsideNum,points_num as pointsNum,qr_code_url as qrCodeUrl,rated_load as ratedLoad,rescue_phone_num as rescuePhoneNum, ");
        builder.append(" run_speed as runSpeed,safe_user_id as safeUserId,safe_user_tel as safeUserTel,serial_num as serialNum,step_width as stepWidth,url as url, ");
        builder.append(" used_num as usedNum,used_party_id as usedPartyId,branch_office as branchOffice,elevator_place as elevatorPlace,examination_user_first as examinationUserFirst, ");
        builder.append(" examination_user_second as examinationUserSecond,inner_id as innerId,last_inspect_date as lastInspectDate,place_type as placeType,program_name as programName, ");
        builder.append(" last_maintenance_party_name as lastMaintenancePartyName,make_date as makeDate,mark as mark,technique_mark as techniqueMark,is_participate_in as isParticipateIn, ");
        builder.append(" maintenance_day as maintenanceDay,out_elevator_id as outElevatorId,maintenance_party_credit_code as maintenancePartyCreditCode,check_qualified_photo as checkQualifiedPhoto, ");
        builder.append(" create_by as createBy,elevator_type_name as elevatorTypeName,examination_user_first_name as examinationUserFirstName,examination_user_second_name as examinationUserSecondName, ");
        builder.append(" maintenance_user_first_name as maintenanceUserFirstName,maintenance_user_second_name as maintenanceUserSecondName,safe_user_name as safeUserName,  ");
        builder.append(" used_party_credit_code as usedPartyCreditCode,used_party_name as usedPartyName,maintenance_party_name as maintenancePartyName,branch_office_name as branchOfficeName,  ");
        builder.append(" create_date as createDate,is_paperless_maintenance as isPaperlessMaintenance,update_flag as updateFlag ");
        builder.append(" from elevator_text where update_flag is null limit 500 ");
        return builder.toString();
    }

    public String updateById(Map map){
        StringBuilder builder = new StringBuilder("update elevator ");
        String maintenancePartyId = (String)map.get("maintenancePartyId");
        String usedPartyId = (String)map.get("usedPartyId");
        String outsideNum = (String)map.get("outsideNum");
        String createBy = (String)map.get("createBy");
        String id = (String)map.get("id");
        builder.append(" set maintenance_party_id = #{maintenancePartyId},used_party_id=#{usedPartyId},create_by=#{createBy} ");
        if(outsideNum != null && !"".equals(outsideNum)){
            builder.append(" ,outside_num=#{outsideNum} ");
        }
        builder.append(" where id=#{id} ");
        return builder.toString();
    }

    /**
     * 查询无主电梯信息
     * @param extElevator
     * @return
     */

    public String noownerElevatorQuery(ExtElevator extElevator){
        String select = "SELECT a.id,a.outside_num,a.elevator_num,a.serial_num,a.elevator_type_id,py.party_name as usedPartyName,pt.party_name as maintenancePartyName ," +
                "a.next_check_date,a.is_paperless_maintenance,a.elevator_type_id FROM elevator a LEFT JOIN party py ON py.id = a.used_party_id LEFT JOIN party pt ON pt.id = a.maintenance_party_id WHERE 1=1";
        if (extElevator.getElevatorNum() != null && !extElevator.getElevatorNum().equals("")) {
            select+=" and (a.elevator_num like '%"+extElevator.getElevatorNum()+"%' or a.outside_num like '%"+extElevator.getElevatorNum()+"%')";
        }
        if(extElevator.getUseUnit()!=null && !extElevator.getUseUnit().equals("")){
            select+=" and (py.party_name like  CONCAT(CONCAT('%',#{useUnit},'%')))";

        }
        if(extElevator.getMaintenanceUnit()!=null && !extElevator.getMaintenanceUnit().equals("")){
            select+=" and (pt.party_name like CONCAT(CONCAT('%',#{maintenanceUnit},'%')))";
        }
        select +=" order by a.elevator_num, a.outside_num asc ";
        return select;
    }

    public String noownerElevatorUpdate(Map map){
        StringBuilder builder = new StringBuilder(" UPDATE elevator a SET");
        if(map.get("maintenancePartyName") != null && map.get("maintenancePartyId") != null && map.get("usedPartyId") == null){
            builder.append("  a.maintenance_party_name = #{maintenancePartyName},a.maintenance_party_id = #{maintenancePartyId}");
        }
        if(map.get("usedPartyName") != null && map.get("usedPartyId") != null && map.get("maintenancePartyId") == null){
            builder.append("  a.used_party_name = #{usedPartyName},a.used_party_id = #{usedPartyId}");
        }
        if(map.get("maintenancePartyId") != null && map.get("usedPartyId") != null ){
            builder.append("  a.maintenance_party_name = #{maintenancePartyName},a.maintenance_party_id = #{maintenancePartyId},a.used_party_name = #{usedPartyName},a.used_party_id = #{usedPartyId}");
        }
        String id = (String)map.get("id");
        builder.append(" where a.id = #{id}");

        return builder.toString();
    }

    /**
     *
     * @param map
     * @return
     */
    public String unconfirmedMaintenance(Map map) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ");
        sb.append(" thm.maintenance_party_id as 'maintenancePartyId', ");
        sb.append(" thm.elevator_num as 'elevatorNum', ");
        sb.append(" su.user_name as  'maintenanceUserName', ");
        sb.append(" su2.user_name as 'maintenanceUserName2', ");
        sb.append(" date_format(str_to_date(thm.maintenance_start_date, '%Y%m%d%H%i%s'),'%Y-%m-%d %H:%i:%s') as 'maintenanceStartDate', ");
        sb.append(" date_format(str_to_date(thm.maintenance_end_date,'%Y%m%d%H%i%s'),'%Y-%m-%d %H:%i:%s') as 'maintenanceEndDate' , ");
        sb.append(" thm.type as 'type', ");
        sb.append(" workType as 'workType', ");
        sb.append(" IFNULL(um.message,'0') as 'flag', ");
        sb.append(" (case WHEN um.message =2 then '发送成功未确认' when um.message=3 then '发送失败，该电梯未关联电梯安全管理员，请联系使用单位！' when um.message=4 then '发送失败，该电梯未关联使用单位，请联系平台管理员！' when um.message=1 then '已确认' else '未发送' end) as 'message' ");
        sb.append(" FROM ( ");
        sb.append("     SELECT safe_user_name,maintenance_party_id,elevator_num,maintenance_user_id2,maintenance_user_id1,maintenance_start_date,maintenance_end_date,type,'1' as workType FROM traction_half_mon ");
        sb.append("     UNION all ");
        sb.append("     SELECT safe_user_name,maintenance_party_id,elevator_num,maintenance_user_id2,maintenance_user_id1,maintenance_start_date,maintenance_end_date,type,'2' as workType FROM debris_half_mon ");
        sb.append("     UNION all ");
        sb.append("     SELECT safe_user_name,maintenance_party_id,elevator_num,maintenance_user_id2,maintenance_user_id1,maintenance_start_date,maintenance_end_date,type,'3' as workType FROM escalator_half_mon ");
        sb.append("     UNION all ");
        sb.append("     SELECT safe_user_name,maintenance_party_id,elevator_num,maintenance_user_id2,maintenance_user_id1,maintenance_start_date,maintenance_end_date,type,'4' as workType FROM hydpress_half_mon ");
        sb.append(" ) thm ");
        sb.append(" left join unconfirmed_maintenance um ON thm.elevator_num = um.elevator_num ");
        sb.append(" LEFT JOIN sys_user su ON su.id = thm.maintenance_user_id1 ");
        sb.append(" LEFT JOIN sys_user su2 ON su2.id = thm.maintenance_user_id2 ");
        sb.append(" where thm.safe_user_name IS NULL and thm.maintenance_party_id=" + map.get("maintenancePartyId") + " ");


        // 设备编号
        if (map.get("elevatorNum") != null) {
            sb.append(" and thm.elevator_num like '%" + map.get("elevatorNum").toString() + "%' ");
        }
        /*// 外部关联码
        if (map.get("outsideNum") != null) {
            sb.append(" and er.outside_num like '%" + map.get("outsideNum").toString() + "%' ");
        }*/
        sb.append(" ORDER BY um.message DESC,thm.elevator_num asc");

        return sb.toString();
    }

    /**
     *使用单位查询未确认的维保工单
     * @param map
     * @return
     */
    public String needconfirmedMaintenance(Map map) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ");
        sb.append(" elevator_num as 'elevatorNum', ");
        sb.append(" outside_num as 'outsideNum', ");
        sb.append(" maintenance_party_id as 'maintenancePartyId', ");
        sb.append(" py.party_name as 'partyName', ");
        sb.append(" maintenance_user_name as 'maintenanceUserName', ");
        sb.append(" maintenance_user_name2 as 'maintenanceUserName2', ");
        sb.append(" elevator_type as 'elevatorType', ");
        sb.append(" work_type as 'workType', ");
        sb.append(" (case when um.message=2 then '未确认' else ' ' end ) as 'message' ");
        sb.append(" FROM ");
        sb.append(" unconfirmed_maintenance um  ");
        sb.append(" LEFT JOIN party py ON um.maintenance_party_id = py.id ");
        sb.append(" where um.safe_user_id=" + map.get("safeUserId") + " ");
        sb.append(" and um.message !=1 ");

        // 设备编号
        if (map.get("elevatorNum") != null) {
            sb.append(" and um.elevator_num like '%" + map.get("elevatorNum").toString() + "%' ");
        }
        // 外部关联码
        if (map.get("outsideNum") != null) {
            sb.append(" and um.outside_num like '%" + map.get("outsideNum").toString() + "%' ");
        }

        sb.append(" ORDER BY um.elevator_num asc");

        return sb.toString();
    }

    /**
     *获取单位下的电梯
     * @param map
     * @return
     */
    public String findEleByUnit(Map map) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ");
        sb.append(" er.elevator_num as 'elevatorNum', ");
        sb.append(" er.outside_num as 'outsideNum', ");
        sb.append(" er.used_party_id as 'usedPartyId', ");
        sb.append(" er.safe_user_id as 'safeUserId', ");
        sb.append(" py.party_name as 'partyName' ");
        sb.append(" FROM ");
        sb.append(" elevator er  ");
        sb.append(" LEFT JOIN party py ON er.used_party_id = py.id ");
        sb.append(" where er.maintenance_party_id=" + map.get("maintenancePartyId") + " ");

        return sb.toString();
    }

    /**
     *获取单位下的电梯
     * @param map
     * @return
     */
    public String getLatLngByLevel(Map<String, Object> param){
        String partyId = Integer.toString((Integer) param.get("selectId"));
        String typeId = param.get("typeId").toString();
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT ");
        sb.append(" A.ELEVATOR_NUM AS elevatorNum, ");
        sb.append(" A.LNG AS lng, ");
        sb.append(" A.LAT AS lat, ");
        sb.append(" A.ELEVATOR_PLACE AS elevatorPlace, ");
        sb.append(" A.PLACE_TYPE AS placeType, ");
        sb.append(" B.TYPE_NAME AS typeName ");
        sb.append(" FROM ");
        sb.append(" ELEVATOR A  ");
        sb.append(" LEFT JOIN ELEVATOR_TYPE B ON A.ELEVATOR_TYPE_ID = B.ID ");
        sb.append(" where  A.branch_office=" + partyId + " and A.elevator_type_id = "+typeId+"  and A.lng is not null and A.lat is not null ");

        return sb.toString();
    }

    /**
     * 修改电梯信息
     * @param inMap
     * @return
     */
    public String interfaceUpdateByNum(ReceiveHesuElevator hesuElevator){
        StringBuilder builder = new StringBuilder();
        builder.append(" update elevator a set ");
        builder.append(" outside_num = #{outsideNum}, ");
        builder.append(" serial_num = #{serialNum}, ");
        builder.append(" elevator_province = #{elevatorProvince}, ");
        builder.append(" elevator_city = #{elevatorCity}, ");
        builder.append(" elevator_area = #{elevatorArea}, ");
        builder.append(" elevator_street = #{elevatorStreet}, ");
        builder.append(" elevator_addr = #{elevatorAddr}, ");
        builder.append(" elevator_model = #{elevatorModel}, ");
        builder.append(" elevator_type_name = #{elevatorTypeName}, ");
        builder.append(" maintenance_party_name = #{maintenancePartyName}, ");
        builder.append(" maintenance_party_credit_code = #{maintenancePartyCreditCode}, ");
        builder.append(" used_party_name = #{usedPartyName}, ");
        builder.append(" used_party_credit_code = #{usedPartyCreditCode}, ");
        builder.append(" elevator_building_num = #{elevatorBuildingNum}, ");
        builder.append(" create_company_name = #{createCompanyName}, ");
        builder.append(" examination_party_name = #{examinationPartyName}, ");
        builder.append(" next_check_date = #{nextCheckDate}, ");
        builder.append(" check_qualified_photo = #{checkQualifiedPhoto}, ");
        builder.append(" rescue_phone_num = #{rescuePhoneNum}, ");
        builder.append(" is_stop = #{isStop}, ");
        builder.append(" brand_name = #{brandName}, ");
        builder.append(" maint_tel = #{maintTel}, ");
        builder.append(" insurance_name = #{insuranceName}, ");
        builder.append(" insurance_date = #{insuranceDate}, ");
        builder.append(" build_addr = #{buildAddr}, ");
        builder.append(" used_num = #{usedNum}, ");
        builder.append(" be_used_date = #{beUsedDate}, ");
        builder.append(" governor_ck_date = #{governorCkDate}, ");
        builder.append(" floor_station = #{floorStation}, ");
        builder.append(" safe_user_tel = #{safeUserTel}, ");
        builder.append(" build_party_name = #{buildPartyName}, ");
        builder.append(" rated_load = #{ratedLoad}, ");
        builder.append(" run_speed = #{runSpeed}, ");
        builder.append(" angle = #{angle}, ");
        builder.append(" step_width = #{stepWidth}, ");
        builder.append(" lift_height = #{liftHeight}, ");
        builder.append(" drive_way = #{driveWay}, ");
        builder.append(" lat = #{lat}, ");
        builder.append(" lng = #{lng}, ");

        // 电梯类型
        if (hesuElevator.getElevatorTypeId() !=null) {
            builder.append(" elevator_type_id = #{elevatorTypeId}, ");
        }
        builder.append(" branch_office_name = #{branchOfficeName} ");

        builder.append(" where out_elevator_id = #{outElevatorId} ");
        return builder.toString();
    }

}
